package com.us.usws.support.uitls;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;



/**
 * SQL helper
 * @author fengyong
 *
 */
public class SQLHelpUtil {
	
	private static List<String> filedNames;
	
	private static List<Object> filedValues;

	/**
	 * create SQL by filter
	 * @param filter
	 * @return SQL
	 */
	@SuppressWarnings("incomplete-switch")
	public static String createSQL(Map<FilterDto, Object> filter) {
		StringBuffer sb = new StringBuffer();
		Set<FilterDto> filterKeys = filter.keySet();
		filedNames = new ArrayList<String>();
		filedValues = new ArrayList<Object>();
		int index = 0;
		for(FilterDto filterDto:filterKeys) {
			String fieldName = filterDto.getFiledName();
			Object fieldValue = filter.get(filterDto);
			switch(filterDto.getModel()) {
				case EQ : 
					sb.append(fieldName).append(" = ? ");
					break;
				case NE :
					sb.append(fieldName).append(" <> ? ");
					break;
				case LT :
					sb.append(fieldName).append(" < ? ");
					break;
				case GT :
					sb.append(fieldName).append(" > ? ");
					break;
				case LE :
					sb.append(fieldName).append(" <= ? ");
					break;
				case GE :
					sb.append(fieldName).append(" >= ? ");
					break;
				case LIKE :
					sb.append(fieldName).append(" like ? ");
					break;
			}
			
			filedNames.add(fieldName);
			
			if(filterDto.getModel().equals(FilterModel.LIKE)) {
				filedValues.add("%"+fieldValue+"%");
			} else {
				filedValues.add(fieldValue);
			}
			
			if(sb.length() > 0 && index < filterKeys.size()-1) {
				sb.append(" and ");
			}
			index ++;
		}
		return sb.toString();
	}
	
	/**
	 * create contain collection sql by filter
	 * @param filter
	 * @return SQL
	 */
	public static String createCollectionSQL(Map<FilterDto, Object> filter) {
		StringBuffer sb = new StringBuffer();
		Set<FilterDto> filterKeys = filter.keySet();
		filedNames = new ArrayList<String>();
		filedValues = new ArrayList<Object>();
		int index = 0;
		for(FilterDto filterDto:filterKeys) {
			String fieldName = filterDto.getFiledName();
			Object fieldValue = filter.get(filterDto);
			switch(filterDto.getModel()) {
				case EQ : 
					sb.append(fieldName).append(" = :").append(fieldName.trim());
					break;
				case NE :
					sb.append(fieldName).append(" <> :").append(fieldName.trim());
					break;
				case LT :
					sb.append(fieldName).append(" < :").append(fieldName.trim());
					break;
				case GT :
					sb.append(fieldName).append(" > :").append(fieldName.trim());
					break;
				case LE :
					sb.append(fieldName).append(" <= :").append(fieldName.trim());
					break;
				case GE :
					sb.append(fieldName).append(" >= :").append(fieldName.trim());
					break;
				case IN :
					sb.append(fieldName).append(" in (:").append(fieldName.trim()+")");
					break;
				case LIKE :
					sb.append(fieldName).append(" like :").append(fieldName.trim());
					break;
			}
			
			filedNames.add(fieldName);
			
			if(filterDto.getModel().equals(FilterModel.LIKE)) {
				filedValues.add("%"+fieldValue+"%");
			} else {
				filedValues.add(fieldValue);
			}
			
			if(sb.length() > 0 && index < filterKeys.size()-1) {
				sb.append(" and ");
			}
			index ++;
		}
		return sb.toString();
	}

	public static List<String> getFiledNames() {
		return filedNames;
	}

	public static List<Object> getFiledValues() {
		return filedValues;
	}
}
